from openpyxl import load_workbook
from openpyxl.utils.cell import column_index_from_string


def multiple_sort(s_sheet, t_sheet, s_num, t_wkbook, path):
    """
    先获取答卷中某个题(列)的全部数据,然后写入到out.xlsx中指定sheet的第一列
    """
    for cols in s_sheet.iter_cols(min_col=s_num,
                                  max_col=s_num,
                                  min_row=1,
                                  max_row=s_sheet.max_row,
                                  values_only=True):
        for n, cell in enumerate(cols):
            t_sheet.cell(row=n + 1, column=1, value=cell)
    """ 
    out.xlsx中指定sheet的第一列中的选项进行分列和重新按列排序
    """
    for cols in t_sheet.iter_cols(min_col=1,
                                  max_col=1,
                                  min_row=2,
                                  max_row=s_sheet.max_row,
                                  values_only=True):
        for n, cell in enumerate(cols):
            choice_list = cell.split('┋')
            i_row = 1
            for choice in choice_list:
                while i_row < len(t_sheet[1]):
                    t = t_sheet[1]
                    if str(t[i_row].value) in str(choice):
                        t_sheet.cell(row=n + 2, column=i_row + 1, value=choice)
                        i_row += 1
                        break
                    elif str(choice) == '(跳过)':
                        break
                    else:
                        i_row += 1
    t_wkbook.save(path)


def transposition(wkbook, sheet, path):
    """
    针对输出的表,将第一列选项转置到B1单元格开始的第一行
    """
    max_r = sheet.max_row
    for cols in sheet.iter_cols(min_col=1,
                                max_col=1,
                                min_row=1,
                                max_row=max_r,
                                values_only=True):
        for n, cell in enumerate(cols):
            sheet.cell(row=1, column=n + 2, value=cell)
    sheet.delete_cols(1, 1)
    sheet.insert_cols(1, 1)
    wkbook.save(path)


if __name__ == '__main__':
    print("这个程序可以将Excel答卷数据中的选择题重新排序,按照题目中选项的顺序,请按步骤进行操作!")
    print("请将Excel关闭再执行以下操作!")
    f_path = input("请将按选项文本下载的文件直接拖进来:")
    f_path = f_path.strip('"')
    index = f_path.rfind('\\')
    part = f_path[:index]
    part = part + '\\out.xlsx'
    c_input = 'Y'
    while c_input == 'Y':
        col_en = input("请输入选择题所在列的大写英文标号:")
        col_num = column_index_from_string(col_en)
        i = eval(input("需要将整理后的选择题放在第N个表中,请输入数字N:"))
        """ 
        s=source,s_wb表示答卷所在xlsx 
        """
        s_wb = load_workbook(f_path)
        s_sht = s_wb.active
        """ 
        t=target,t_wb表示格式调整完成后输出到的xlsx 
        """
        t_wb = load_workbook(part)  # 读取将格式调整后输出在的xlsx
        t_shortname_list = t_wb.sheetnames
        t_sht = t_wb[t_shortname_list[i - 1]]
        transposition(t_wb, t_sht, part)
        multiple_sort(s_sht, t_sht, col_num, t_wb, part)
        c_input = input('是否继续处理下个sheet,请输入[Y/N]:')